<?php
    class Subject_book_model extends CI_Model{

        function __construct(){
            parent::__construct();
        }  

        function get_book_detail($book_id){

            $sql = "SELECT  * FROM {$this->db->dbprefix}b_book where id= {$book_id} ";
            return $this->db->query($sql)->row_array();
        }

        /**
         * 根据科目id获取课本list
         * @param  [type] $subject_id [description]
         * @return [type]             [description]
         */
        function get_book_list_by_subject($subject_id){
            $sql = "SELECT  * FROM {$this->db->dbprefix}b_book where subject_id= {$subject_id} ";
            return $this->db->query($sql)->result_array();
        }
        /**
         * 获取课本
         * @param  integer $limit      [description]
         * @param  integer $offset     [description]
         * @param  array   $conditions [description]
         * @return [type]              [description]
         */
        function get_book_list($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if(!empty($conditions['title'])){
                $where .= " AND s.title LIKE '%{$conditions['title']}%'";
            }
            if(isset($conditions['subject']) && $conditions['subject']>0){
                $where .= " AND s.subject_id={$conditions['subject']}";
            }

            $sql = "SELECT SQL_CALC_FOUND_ROWS s.*,bs.name as subject_name FROM {$this->db->dbprefix}b_book AS s LEFT JOIN {$this->db->dbprefix}b_subject bs on bs.id=s.subject_id ";
            $sql .= "WHERE $where ORDER BY s.grade asc,s.semester asc ";
            if($limit > 0 || $offset > 0){
               $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }

        function get_chapter_list_by_book($book_id){
            $sql = "SELECT  * FROM {$this->db->dbprefix}b_book_chapter where book_id = {$book_id} order by sort asc ";
            return $this->db->query($sql)->result_array();
        }

        function get_chapter_by_book_parent($book_id,$parent_id=0){
            $sql = "SELECT  * FROM {$this->db->dbprefix}b_book_chapter where book_id = {$book_id} and parent_id= {$parent_id} ";
            return $this->db->query($sql)->result_array();
        }

        /**
         * 获取课本章节
         * @param  integer $limit      [description]
         * @param  integer $offset     [description]
         * @param  array   $conditions [description]
         * @return [type]              [description]
         */
        function get_chapter_list($limit = 0, $offset = 0, $conditions = array()){
            $where = "1 = 1";
            if($conditions){
                if(!empty($conditions['title'])){
                    $where .= " AND c.title LIKE '%{$conditions['title']}%'";
                }

                if(!empty($conditions['subject_id'])){
                    $where .= " AND c.book_id = '{$conditions['subject_id']}'";
                }

                if(!empty($conditions['parent_id'])){
                    $where .= " AND c.parent_id = '{$conditions['parent_id']}'";
                }
            }
            $sql = "SELECT SQL_CALC_FOUND_ROWS c.*, s.title AS sub_title FROM {$this->db->dbprefix}b_book_chapter AS c ";
            $sql .= " LEFT JOIN {$this->db->dbprefix}b_book AS s ON(s.id=c.book_id) ";
            $sql .= " WHERE $where ORDER BY c.sort ASC ";
            if($limit > 0 || $offset > 0){
               $sql .= "LIMIT $limit, $offset";
            }
            $result = $this->db->query($sql)->result_array();
            $row_num =  $this->db->query('SELECT FOUND_ROWS() as row_num;')->row_array();
            return array("count" => $row_num['row_num'], "result" => $result);
        }
    }